package com.yao.club.mapper;

import com.yao.club.entity.vo.PostsInfoVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.mapstruct.Mapper;

import java.util.List;

@Mapper
public interface PostsInfoMapper {
    @Select("select * from posts where posts_id =#{posts_id}")
    PostsInfoVO select(@Param("posts_id") String posts_id);


    @Update("UPDATE posts SET comment_number = comment_number + 1 WHERE  posts_id= #{posts_id}")
    int incrementCommentCount(@Param("posts_id") String posts_id);

    @Update("UPDATE posts SET like_number = like_number + 1 WHERE  posts_id= #{posts_id}")
    int incrementLikeCount(@Param("posts_id") Long posts_id);

    @Update("UPDATE posts SET like_number = like_number - 1 WHERE  posts_id= #{posts_id}")
    int unIncrementLikeCount(@Param("posts_id") Long posts_id);

    @Update("UPDATE posts SET share_number = share_number + 1 WHERE  posts_id= #{posts_id}")
    int incrementShareCount(@Param("posts_id") Long posts_id);

    @Update("UPDATE posts SET share_number = share_number - 1 WHERE  posts_id= #{posts_id}")
    int unIncrementShareCount(@Param("posts_id") Long posts_id);


    @Update("UPDATE posts SET collect_number = collect_number + 1 WHERE  posts_id= #{posts_id}")
    int incrementCollectCount(@Param("posts_id") Long posts_id);

    @Update("UPDATE posts SET collect_number = collect_number - 1 WHERE  posts_id= #{posts_id}")
    int unIncrementCollectCount(@Param("posts_id") Long posts_id);

    /**
     * 统计未被指定用户屏蔽的有效帖子数量
     */
    @Select("SELECT COUNT(*) FROM posts p " +
            "WHERE p.del_flag = 0  " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s WHERE s.post_id = p.posts_id AND s.user_id = #{userId})")
    int countPostsExcludeShielded(@Param("userId") String userId);

    /**
     * 统计搜索结果总数（排除屏蔽的帖子）
     */
    @Select("SELECT COUNT(*) FROM posts p " +
            "WHERE p.del_flag = 0  " +
            "AND (p.content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR EXISTS (SELECT 1 FROM posts_topic t " +
            "           WHERE FIND_IN_SET(t.topicId, p.topic_ids) " +
            "           AND t.topicName LIKE CONCAT('%', #{keyword}, '%'))) " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s " +
            "               WHERE s.post_id = p.posts_id " +
            "               AND s.user_id = #{userId})")
    int countSearchPostsExcludeShielded(@Param("keyword") String keyword,
                                        @Param("userId") String userId);

    /**
     * 统计搜索结果总数（排除屏蔽的帖子）
     */
    @Select("SELECT COUNT(*) FROM posts p " +
            "WHERE p.del_flag = 0 AND p.create_by = #{memberId} " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s " +
            "               WHERE s.post_id = p.posts_id " +
            "               AND s.user_id = #{userId})")
    int countSearchPostsMemberIdExcludeShielded(@Param("memberId") String memberId,
                                        @Param("userId") String userId);
    /**
     * 统计话题下的帖子数量（排除屏蔽的帖子）
     */
        @Select("SELECT COUNT(*) FROM posts p " +
                "WHERE p.del_flag = 0  " +
            "AND FIND_IN_SET(#{topicId}, p.topic_ids) " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s " +
            "               WHERE s.post_id = p.posts_id " +
            "               AND s.user_id = #{userId})")
    int countTopicPostsExcludeShielded(@Param("topicId") String topicId, 
                                      @Param("userId") String userId);

    /**
     * 分页查询话题下的帖子（排除屏蔽的帖子）
     */
    @Select({
        "<script>",
        "SELECT p.* FROM posts p ",
          "WHERE p.del_flag = 0  " +
        "AND FIND_IN_SET(#{topicId}, p.topic_ids) ",
        "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s ",
        "               WHERE s.post_id = p.posts_id ",
        "               AND s.user_id = #{userId}) ",
        "<if test='sortType == \"hot\"'>",
        "ORDER BY (p.like_number * 2 + p.comment_number * 3 + p.collect_number * 4 + p.share_number) DESC, p.create_time DESC",
        "</if>",
        "<if test='sortType != \"hot\"'>",
        "ORDER BY p.create_time DESC",
        "</if>",
        "LIMIT #{offset}, #{pageSize}",
        "</script>"
    })
    List<PostsInfoVO> pageTopicPostsExcludeShielded(@Param("topicId") String topicId,
                                                    @Param("userId") String userId,
                                                    @Param("offset") int offset,
                                                    @Param("pageSize") int pageSize,
                                                    @Param("sortType") String sortType);

    /**
     * 统计话题下的所有有效帖子数量
     */
    @Select("SELECT COUNT(*) FROM posts p " +
            "WHERE p.del_flag = 0  " +
            "AND FIND_IN_SET(#{topicId}, p.topic_ids)")
    int countTopicAllValidPosts(@Param("topicId") String topicId);

    /**
     * 分页查询话题下的所有有效帖子
     */
    @Select({
        "<script>",
        "SELECT p.* FROM posts p ",
        "WHERE p.del_flag = 0  " +
        "AND FIND_IN_SET(#{topicId}, p.topic_ids) ",
        "<if test='sortType == \"hot\"'>",
        "ORDER BY (p.like_number * 2 + p.comment_number * 3 + p.collect_number * 4 + p.share_number) DESC, p.create_time DESC",
        "</if>",
        "<if test='sortType != \"hot\"'>",
        "ORDER BY p.create_time DESC",
        "</if>",
        "LIMIT #{offset}, #{pageSize}",
        "</script>"
    })
    List<PostsInfoVO> pageTopicAllValidPosts(@Param("topicId") String topicId,
                                             @Param("offset") int offset,
                                             @Param("pageSize") int pageSize,
                                             @Param("sortType") String sortType);
    /**
     * 分页查询未被指定用户屏蔽的有效帖子
     */
    @Select({
            "<script>",
            "SELECT p.* FROM posts p ",
            "WHERE p.del_flag = 0 ",
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s WHERE s.post_id = p.posts_id AND s.user_id = #{userId}) ",
            "<if test='sortType == \"hot\"'>",
            "ORDER BY (p.like_number * 2 + p.comment_number * 3 + p.collect_number * 4 + p.share_number) DESC, p.create_time DESC",
            "</if>",
            "<if test='sortType != \"new\"'>",
            "ORDER BY p.create_time DESC",
            "</if>",
            "LIMIT #{offset}, #{pageSize}",
            "</script>"
    })
    List<PostsInfoVO> pagePostsExcludeShielded(@Param("userId") String userId,
                                               @Param("offset") int offset,
                                               @Param("pageSize") int pageSize,
                                               @Param("sortType") String sortType);


    @Select("SELECT p.* FROM posts p " +
            "WHERE p.del_flag = 0 AND p.create_by = #{memberId} "+
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s " +
            "               WHERE s.post_id = p.posts_id " +
            "               AND s.user_id = #{userId}) " +
            "ORDER BY p.create_time DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<PostsInfoVO> searchPostsMemberIdExcludeShielded(@Param("memberId") String memberId,
                                                 @Param("userId") String userId,
                                                 @Param("offset") int offset,
                                                 @Param("pageSize") int pageSize);


    /**
     * 搜索帖子（排除屏蔽的帖子）
     */
    @Select("SELECT p.* FROM posts p " +
            "WHERE p.del_flag = 0 "+
            "AND (p.content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR EXISTS (SELECT 1 FROM posts_topic t " +
            "           WHERE FIND_IN_SET(t.topicId, p.topic_ids) " +
            "           AND t.topicName LIKE CONCAT('%', #{keyword}, '%'))) " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s " +
            "               WHERE s.post_id = p.posts_id " +
            "               AND s.user_id = #{userId}) " +
            "ORDER BY p.create_time DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<PostsInfoVO> searchPostsExcludeShielded(@Param("keyword") String keyword,
                                                 @Param("userId") String userId,
                                                 @Param("offset") int offset,
                                                 @Param("pageSize") int pageSize);



    /**
     * 统计所有有效帖子数量
     */
    @Select("SELECT COUNT(*) FROM posts WHERE del_flag = 0")
    int countAllValidPosts();


    /**
     * 统计所有符合搜索条件的帖子数量
     */
    @Select("SELECT COUNT(*) FROM posts p " +
            "WHERE p.del_flag = 0 " +
            "AND (p.content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR EXISTS (SELECT 1 FROM posts_topic t " +
            "           WHERE FIND_IN_SET(t.topicId, p.topic_ids) " +
            "           AND t.topicName LIKE CONCAT('%', #{keyword}, '%')))")
    int countSearchAllValidPosts(@Param("keyword") String keyword);


    /**
     * 统计所有符合搜索条件的帖子数量
     */
    @Select("SELECT COUNT(*) FROM posts p " +
            "WHERE p.del_flag = 0 " +
            "AND p.create_by = #{memberId}")
    int countSearchAllValidPostsByMemberId(@Param("memberId") String memberId);


    /**
     * 搜索所有符合条件的帖子
     */
    @Select("SELECT p.* FROM posts p " +
            "WHERE p.del_flag = 0 " +
            "AND (p.content LIKE CONCAT('%', #{keyword}, '%') " +
            "OR EXISTS (SELECT 1 FROM posts_topic t " +
            "           WHERE FIND_IN_SET(t.topicId, p.topic_ids) " +
            "           AND t.topicName LIKE CONCAT('%', #{keyword}, '%'))) " +
            "ORDER BY p.create_time DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<PostsInfoVO> searchAllValidPosts(@Param("keyword") String keyword,
                                          @Param("offset") int offset,
                                          @Param("pageSize") int pageSize);

    /**
     * 搜索所有符合条件的帖子
     */
    @Select("SELECT p.* FROM posts p " +
            "WHERE p.del_flag = 0 " +
            "AND p.create_by = #{memberId} " +
            "ORDER BY p.create_time DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<PostsInfoVO> searchAllValidPostsByMemberId(@Param("memberId") String memberId,
                                          @Param("offset") int offset,
                                          @Param("pageSize") int pageSize);

    /**
     * 分页查询所有有效帖子
     */
    @Select({
            "<script>",
            "SELECT * FROM posts ",
            "WHERE del_flag = 0 ",
            "<if test='sortType == \"hot\"'>",
            "ORDER BY (like_number * 2 + comment_number * 3 + collect_number * 4 + share_number) DESC, create_time DESC",
            "</if>",
            "<if test='sortType != \"hot\"'>",
            "ORDER BY create_time DESC",
            "</if>",
            "LIMIT #{offset}, #{pageSize}",
            "</script>"
    })
    List<PostsInfoVO> pageAllValidPosts(@Param("offset") int offset,
                                        @Param("pageSize") int pageSize,
                                        @Param("sortType") String sortType);

    @Select("SELECT COUNT(*) FROM posts p,user_follow f " +
            "WHERE p.del_flag = 0 AND f.follower_id = #{userId} " +
            "AND p.create_by = f.followed_id " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s WHERE s.post_id = p.posts_id AND s.user_id = #{userId})")
    int countFollowingPostsExcludeShielded(@Param("userId") String userId);

    @Select("SELECT p.* FROM posts p,user_follow f " +
            "WHERE p.del_flag = 0 AND f.follower_id = #{userId} " +
            "AND p.create_by = f.followed_id " +
            "AND NOT EXISTS (SELECT 1 FROM posts_shield_record s WHERE s.post_id = p.posts_id AND s.user_id = #{userId}) " +
            "ORDER BY p.createTime DESC " +
            "LIMIT #{offset}, #{pageSize}")
    List<PostsInfoVO> pageFollowingPostsExcludeShielded(@Param("userId") String userId, 
                                                        @Param("offset") int offset, 
                                                        @Param("pageSize") int pageSize);

}
